/**
 * Application name : Recipes App
 * Date				: March 2012
 */
package com.recipes.app;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.util.Log;
import android.widget.Toast;

public class DBHelper extends SQLiteOpenHelper {

	private static String DB_PATH = "/data/data/com.recipes.app/databases/";

	private final static String DB_NAME = "input_db";
	public final static int DB_VERSION = 1;
	public static SQLiteDatabase db;

	private Context context;

	private final String TABLE_NAME = "tbl_recipes";
	private final String TBL_CA_NAME = "tbl_category";
	private final String TBL_ABOUT = "tbl_about";
	private final String ID = "id";
	private final String RECIPE_NAME = "sub_item_name";
	private final String IMAGE_PREVIEW_1 = "image_1";
	private final String IMAGE_PREVIEW_2 = "image_2";
	private final String IMAGE_PREVIEW_3 = "image_3";
	private final String PREP_TIME = "prepare";
	private final String COOK_TIME = "action";
	private final String SERVES = "serves";
	private final String SUMMARY = "summary";
	private final String INGREDIENTS = "content_details";
	private final String DIRECTIONS = "directions";

	private final String CA_NO = "category_no";

	private boolean hasSD = false;
	//private static String SDPATH = "/mnt/sdcard";

	public DBHelper(Context context) {

		super(context, DB_NAME, null, DB_VERSION);
		this.context = context;

		hasSD = Environment.getExternalStorageState().equals(

		android.os.Environment.MEDIA_MOUNTED);

		// SDPATH = Environment.getExternalStorageDirectory().getPath();
	}

	public void createDataBase() throws IOException {

		boolean dbExist = checkDataBase();
		SQLiteDatabase db_Read = null;

		if (dbExist) {
			// do nothing - database already exist
			deleteDataBase();
			try {
				copyDataBase();
			} catch (IOException e) {
				throw new Error("Error copying database");
			}
		} else {
			db_Read = this.getReadableDatabase();
			db_Read.close();

			try {
				copyDataBase();
			} catch (IOException e) {
				throw new Error("Error copying database");
			}
		}

	}

	private void deleteDataBase() {
		File dbFile = new File(DB_PATH + DB_NAME);

		dbFile.delete();
	}

	private boolean checkDataBase() {

		File dbFile = new File(DB_PATH + DB_NAME);

		return dbFile.exists();

	}

	private void copyDataBase() throws IOException {

		InputStream myInput = context.getAssets().open("input_db");

		// InputStream myInput = context.getAssets().open(DB_NAME);

		String outFileName = DB_PATH + DB_NAME;

		OutputStream myOutput = new FileOutputStream(outFileName);

		byte[] buffer = new byte[1024];
		int length;
		while ((length = myInput.read(buffer)) > 0) {
			myOutput.write(buffer, 0, length);
		}

		myOutput.flush();
		myOutput.close();
		myInput.close();

	}

	public void openDataBase() throws SQLException {
		String myPath = DB_PATH + DB_NAME;
		db = SQLiteDatabase.openDatabase(myPath, null,
				SQLiteDatabase.OPEN_READONLY);
	}

	@Override
	public void close() {
		db.close();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}

	/** this code is used to get all data from database */
	public ArrayList<ArrayList<Object>> getAllData(String RecipeNameKeyword,
			int id) {
		ArrayList<ArrayList<Object>> dataArrays = new ArrayList<ArrayList<Object>>();

		Cursor cursor = null;

		if (RecipeNameKeyword.equals("")) {
			try {
				cursor = db.query(TABLE_NAME, new String[] { ID, RECIPE_NAME,
						IMAGE_PREVIEW_1, COOK_TIME, CA_NO }, CA_NO + "=?",
						new String[] { String.valueOf(id) }, null, null, null);
				cursor.moveToFirst();

				if (!cursor.isAfterLast()) {
					do {
						ArrayList<Object> dataList = new ArrayList<Object>();

						dataList.add(cursor.getLong(0));
						dataList.add(cursor.getString(1));
						dataList.add(cursor.getString(2));
						dataList.add(cursor.getString(3));

						dataArrays.add(dataList);
					}

					while (cursor.moveToNext());
				}
				cursor.close();
			} catch (SQLException e) {
				Log.e("DB Error", e.toString());
				e.printStackTrace();
			}
		} else {
			try {
				cursor = db.query(TABLE_NAME, new String[] { ID, RECIPE_NAME,
						IMAGE_PREVIEW_1, COOK_TIME }, RECIPE_NAME + " LIKE '%"
						+ RecipeNameKeyword + "%'", null, null, null, null);
				cursor.moveToFirst();

				if (!cursor.isAfterLast()) {
					do {
						ArrayList<Object> dataList = new ArrayList<Object>();

						dataList.add(cursor.getLong(0));
						dataList.add(cursor.getString(1));
						dataList.add(cursor.getString(2));
						dataList.add(cursor.getString(3));

						dataArrays.add(dataList);
					}

					while (cursor.moveToNext());
				}
				cursor.close();
			} catch (SQLException e) {
				Log.e("DB Error", e.toString());
				e.printStackTrace();
			}
		}
		return dataArrays;
	}

	/** this code is used to get category from database */
	public ArrayList<ArrayList<Object>> getCategory() {
		ArrayList<ArrayList<Object>> dataArrays = new ArrayList<ArrayList<Object>>();

		Cursor cursor = null;

		try {
			cursor = db.query(TBL_CA_NAME, new String[] { "category_no",
					"category_name", "image_preview" }, null, null, null, null,
					null);
			cursor.moveToFirst();

			if (!cursor.isAfterLast()) {
				do {
					ArrayList<Object> dataList = new ArrayList<Object>();

					dataList.add(cursor.getInt(0));
					dataList.add(cursor.getString(1));
					dataList.add(cursor.getString(2));
					dataArrays.add(dataList);
				}

				while (cursor.moveToNext());
			}
			cursor.close();
		} catch (SQLException e) {
			Log.e("DB Error", e.toString());
			e.printStackTrace();
		}
		return dataArrays;
	}

	/** this code is used to get data from database base on id value */
	public ArrayList<Object> getDetail(long id) {

		ArrayList<Object> rowArray = new ArrayList<Object>();
		Cursor cursor;

		try {
			cursor = db.query(TABLE_NAME, new String[] { RECIPE_NAME,
					IMAGE_PREVIEW_1, IMAGE_PREVIEW_2, IMAGE_PREVIEW_3,
					PREP_TIME, COOK_TIME, SERVES, SUMMARY, INGREDIENTS,
					DIRECTIONS }, ID + "=" + id, null, null, null, null, null);

			cursor.moveToFirst();

			if (!cursor.isAfterLast()) {
				do {
					rowArray.add(cursor.getString(0));
					rowArray.add(cursor.getString(1));
					rowArray.add(cursor.getString(2));
					rowArray.add(cursor.getString(3));
					rowArray.add(cursor.getString(4));
					rowArray.add(cursor.getString(5));
					rowArray.add(cursor.getString(6));
					rowArray.add(cursor.getString(7));
					rowArray.add(cursor.getString(8));
					rowArray.add(cursor.getString(9));
				} while (cursor.moveToNext());
			}

			cursor.close();
		} catch (SQLException e) {
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}

		return rowArray;
	}

	public String getAbout() {
		String about = "";
		Cursor cursor;
		try {
			cursor = db.query(TBL_ABOUT, new String[] { "about_info" }, null,
					null, null, null, null);
			cursor.moveToFirst();
			if (!cursor.isAfterLast()) {
				do {
					about = cursor.getString(0);
				}

				while (cursor.moveToNext());
			}

			cursor.close();
		} catch (SQLException e) {
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}
		return about;

	}
}